<html>
  <head><title>SP Objects</title>
    <script src="codepress/codepress.js" type="text/javascript"></script> 
    <link rel="stylesheet" href="style.css" type="text/css" media="all" />
  </head>
  <body>

    <h1>Procedure Object: D_QT_RENWL_STATUS.SQL</h1>
    <p><b>Author:</b> Bill Wang</p>
    <p><b>Date:</b> 04/18/2007</p>
    <p><b>Abstract:</b></p>
    <p>          Set quote status to inactived
 </b></p>
    <p><b>Sample Calls:</b></p>
    <p>          call EBIZ1.D_QT_RENWL_STATUS (?, '0000057043','E0003','S')<br> </b></p>
    <p></p>
    
    
    <h3>Tables:</h3>
    <ul>
      
        <li><b><a href="WEB_QUOTE_STAT.html">EBIZ1.WEB_QUOTE_STAT</a></b></li>
      
    </ul>
    
    
    
    
    
    
    
    
    <h3>Source code:</h3>
    <div id="SQLsrc" >
<em>DROP</em>&nbsp;PROCEDURE&nbsp;EBIZ1.D_QT_RENWL_STATUS<br />@<br /><em>CREATE</em>&nbsp;PROCEDURE&nbsp;EBIZ1.D_QT_RENWL_STATUS&nbsp;(<br />&nbsp;&nbsp;&nbsp;OUT&nbsp;poGenStatus&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>INTEGER</u>,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;1</i><br />&nbsp;&nbsp;&nbsp;<b>IN</b>&nbsp;&nbsp;piWebQuoteNum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHARACTER</u>(10),<br />&nbsp;&nbsp;&nbsp;<b>IN</b>&nbsp;&nbsp;piQuoteStatCode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHARACTER</u>(8),<br />&nbsp;&nbsp;&nbsp;<b>IN</b>&nbsp;&nbsp;piStatPrirFlag&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHARACTER</u>(1)<br />)<br /><br />&nbsp;&nbsp;&nbsp;RESULT&nbsp;SETS&nbsp;0<br />&nbsp;&nbsp;&nbsp;<b>LANGUAGE</b>&nbsp;<b>SQL</b><br />&nbsp;&nbsp;&nbsp;SPECIFIC&nbsp;EBIZ1.D_QT_RENWL_STATUS<br /><i>-----------------------------------------------------------------------------------</i><br /><i>--&nbsp;IBM&nbsp;Confidential</i><br /><i>--&nbsp;procedure&nbsp;name&nbsp;:&nbsp;D_QT_RENWL_STATUS</i><br /><i>--&nbsp;file&nbsp;name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D_QT_RENWL_STATUS.<b>SQL</b></i><br /><i>--&nbsp;author:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Bill&nbsp;Wang</i><br /><i>--&nbsp;<u>date</u>:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;04/18/2007</i><br /><i>--</i><br /><i>--&nbsp;abstract:</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>Set</em>&nbsp;quote&nbsp;status&nbsp;<b>to</b>&nbsp;inactived</i><br /><i>--&nbsp;sample&nbsp;calls:</i><br /><i>--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;call&nbsp;EBIZ1.D_QT_RENWL_STATUS&nbsp;(?,&nbsp;<s>'0000057043'</s>,<s>'E0003'</s>,<s>'S'</s>)</i><br /><i>--</i><br /><i>--&nbsp;MAINTENANCE&nbsp;LOG</i><br /><i>--&nbsp;who&nbsp;&nbsp;<u>date</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b><em>comment</em></b></i><br /><i>--&nbsp;---&nbsp;&nbsp;--------&nbsp;&nbsp;---------------------------------------------------------------</i><br /><i>--&nbsp;BW&nbsp;&nbsp;&nbsp;04/18/07&nbsp;&nbsp;Initial&nbsp;Version</i><br /><i>--&nbsp;Will&nbsp;05/23/07&nbsp;&nbsp;<b>Check</b>&nbsp;<b>if</b>&nbsp;it&nbsp;exists&nbsp;before&nbsp;<em>update</em></i><br /><i>-----------------------------------------------------------------------------------</i><br />P1:&nbsp;<em>BEGIN</em>&nbsp;<b>NOT</b>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Variables&nbsp;declarations</i><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;Variables</i><br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;SQLCODE&nbsp;&nbsp;<u>INTEGER</u>&nbsp;<b>DEFAULT</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;SQLSTATE&nbsp;<u>CHAR</u>(5)&nbsp;<b>DEFAULT</b>&nbsp;<s>'00000'</s>;<br /><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;handler&nbsp;variables</i><br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;hGenStatus&nbsp;<u>INTEGER</u>&nbsp;<b>DEFAULT</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;hSqlcode&nbsp;&nbsp;&nbsp;<u>INTEGER</u>&nbsp;<b>DEFAULT</b>&nbsp;0;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;hSqlstate&nbsp;&nbsp;<u>CHAR</u>(5)&nbsp;<b>DEFAULT</b>&nbsp;<s>'00000'</s>;<br /><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;<b>Local</b>&nbsp;Variables</i><br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;vCurrTimestamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>TIMESTAMP</u>;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;vModByUserName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHAR</u>(12)&nbsp;<b>DEFAULT</b>&nbsp;<s>'EBIZ'</s>;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;vStatPrir&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>CHAR</u>(1);&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;vInactFlag&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>SMALLINT</u>&nbsp;<b>DEFAULT</b>&nbsp;1;<br /><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;<b>EXCEPTION</b>&nbsp;HANDLER&nbsp;declaration</i><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Generic&nbsp;Handler</i><br />&nbsp;&nbsp;&nbsp;<em>DECLARE</em>&nbsp;CONTINUE&nbsp;HANDLER&nbsp;<b>FOR</b>&nbsp;SQLEXCEPTION,&nbsp;SQLWARNING,&nbsp;<b>NOT</b>&nbsp;FOUND<br />&nbsp;&nbsp;&nbsp;<em>BEGIN</em>&nbsp;<b>NOT</b>&nbsp;ATOMIC<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>--&nbsp;Capture&nbsp;SQLCODE&nbsp;&&nbsp;SQLSTATE</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SELECT</em>&nbsp;&nbsp;SQLCODE,&nbsp;SQLSTATE<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>INTO</b>&nbsp;&nbsp;&nbsp;&nbsp;hSqlcode,&nbsp;hSqlstate<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>FROM</b>&nbsp;&nbsp;&nbsp;&nbsp;SYSIBM.SYSDUMMY1;<br /><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASE</b>&nbsp;hSqlstate<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'02000'</s>&nbsp;<b>THEN</b>&nbsp;&nbsp;<i>--<b>row</b>&nbsp;<b>not</b>&nbsp;found</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus=5000;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'42724'</s>&nbsp;<b>THEN</b>&nbsp;&nbsp;<i>--missing&nbsp;llsp</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus=3;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>ELSE</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;(hSqlCode&nbsp;<&nbsp;0)&nbsp;<b>THEN</b>&nbsp;&nbsp;<i>--trap&nbsp;<b>only</b>&nbsp;errors,&nbsp;<b>not</b>&nbsp;warnings</i><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus=&nbsp;2;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>CASE</b>;<br />&nbsp;&nbsp;&nbsp;<em>END</em>;<br /><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Initialization</i><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;make&nbsp;sure&nbsp;the&nbsp;<b>input</b>&nbsp;<b>is</b>&nbsp;<b>valid</b></i><br />&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;0;<br /><br />&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;piWebQuoteNum&nbsp;=&nbsp;COALESCE(RTRIM(piWebQuoteNum),&nbsp;<s>''</s>);<br />&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;piQuoteStatCode&nbsp;=&nbsp;COALESCE(RTRIM(piQuoteStatCode),&nbsp;<s>''</s>);<br />&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;piStatPrirFlag&nbsp;=&nbsp;COALESCE(RTRIM(piStatPrirFlag),&nbsp;<s>''</s>);<br /><br /><br />&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;(&nbsp;piWebQuoteNum&nbsp;=&nbsp;<s>''</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>OR</b>&nbsp;piQuoteStatCode&nbsp;=&nbsp;<s>''</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>OR</b>&nbsp;piStatPrirFlag&nbsp;=&nbsp;<s>''</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>OR</b>&nbsp;(piStatPrirFlag&nbsp;<>&nbsp;<s>'P'</s><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>AND</b>&nbsp;piStatPrirFlag&nbsp;<>&nbsp;<s>'S'</s>))<br />&nbsp;&nbsp;&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;poGenStatus&nbsp;=&nbsp;34100;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>RETURN</b>&nbsp;poGenStatus;<br />&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br /><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<i>--&nbsp;Procedure&nbsp;Body</i><br />&nbsp;&nbsp;&nbsp;<i>-------------------------------------------------------------</i><br />&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;vCurrTimestamp&nbsp;=&nbsp;CURRENT&nbsp;<u>TIMESTAMP</u>;<br />&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;<b>CASE</b>&nbsp;piStatPrirFlag<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'P'</s>&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;vStatPrir=<s>'1'</s>;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHEN</b>&nbsp;<s>'S'</s>&nbsp;<b>THEN</b><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;vStatPrir=<s>'2'</s>;<br />&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>CASE</b>;<br />&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;<b>IF</b>&nbsp;EXISTS(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SELECT</em>&nbsp;1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>FROM</b>&nbsp;EBIZ1.WEB_QUOTE_STAT<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHERE</b>&nbsp;WEB_QUOTE_NUM=piWebQuoteNum<br />	&nbsp;<b>AND</b>&nbsp;QUOTE_STAT_CODE=piQuoteStatCode<br />	&nbsp;<b>AND</b>&nbsp;STAT_PRIR=vStatPrir<br />&nbsp;&nbsp;&nbsp;)<b>THEN</b><br />&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>UPDATE</em>&nbsp;EBIZ1.WEB_QUOTE_STAT<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<em>SET</em>&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INACT_FLAG&nbsp;=&nbsp;vInactFlag,<br />&nbsp;&nbsp;&nbsp;	&nbsp;&nbsp;&nbsp;MOD_DATE=vCurrTimestamp	&nbsp;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>WHERE</b>&nbsp;WEB_QUOTE_NUM=piWebQuoteNum<br />	&nbsp;<b>AND</b>&nbsp;QUOTE_STAT_CODE=piQuoteStatCode<br />	&nbsp;<b>AND</b>&nbsp;STAT_PRIR=vStatPrir;&nbsp;&nbsp;<br />&nbsp;&nbsp;&nbsp;<em>END</em>&nbsp;<b>IF</b>;<br /><br />&nbsp;&nbsp;&nbsp;<b>RETURN</b>&nbsp;poGenStatus;<br /><br /><em>END</em>&nbsp;P1<br /><br />@<br />CALL&nbsp;DTOL.g_sp_exec_grp&nbsp;(<s>'EBIZ1'</s>,&nbsp;<s>'D_QT_RENWL_STATUS'</s>,&nbsp;<s>'prcbkweb'</s>)<br />@
    </div> 
  </body>
</html>
